﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.IO;
using System.Data.SqlClient;
using System.Collections;

namespace BusinessTier
{
    public class Review
    {
        private string studentid;
        private string group;
        private string dataConnection = "Data Source=(local);Initial Catalog=INB201;Integrated Security=True";

        public Review(string studentid)
        {
            this.studentid = studentid;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="reviews"></param>
        public void addReview(List<List<string>> reviews)
        {
            string resultsToUser;
            try
            {
                SqlConnection connect = new SqlConnection(this.dataConnection);
                SqlCommand dataCommand = new SqlCommand();
                dataCommand.Connection = connect;
                connect.Open();

                string groupName;

                // Quick check that there isn't a group stored in object memory,
                // if there is no group name saved getGroup() is called
                if (this.group == null || this.group == "")
                {
                    groupName = getGroupName();
                }
                else
                {
                    groupName = this.group;
                }

                

                ////string week = weekdropdown.DataValueField;
                //string week;
                //string user_name;
                //string user_id;
                //string mark;
                //string comment;

                ////insert values into tutorresults table
                //foreach (List<string> result in results)
                //{
                //    week = WeekDropDown.Text;
                //    user_name = result[0];
                //    user_id = result[1];
                //    mark = result[2];
                //    comment = result[3];

                //    dataCommand.CommandText = "INSERT INTO tutorresults(week, user_name, user_id, mark, comment)";
                //    dataCommand.CommandText += "VALUES (" + week + ",'" + user_name + "','" + user_id + "'," + mark + ",'" + comment + "')";
                //    SqlDataReader datareader = dataCommand.ExecuteReader();
                //    datareader.Close();
                //}

                connect.Close();

                groupName = ((string)dataCommand.ExecuteScalar());
                this.group = groupName;
                connect.Close();
            }
            catch (Exception e)
            {
                // label on page is set to exception text
                resultsToUser = e.ToString();

            }
        }

        public string importDetails(StreamReader file)
        {
            string message;

            try
            {
                string line;
                
                ArrayList sepList = new ArrayList();

                //Read the file specified in the FileUpload control and display it line by line.         
                using (file)
                {
                    while ((line = file.ReadLine()) != null)
                    {
                        //Create a delimiter that matches the strictly formatted text file
                        // in this case tabs ( \t )
                        char[] delimiters = new char[] { '\t' };
                        //Create an array of strings which holds each of these values and removes all empty space
                        string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
                        //Add each string stored in the parts array to the ArrayList
                        for (int i = 0; i < parts.Length; i++)
                        {
                            sepList.Add(parts[i]);
                        }
                    }
                    file.Close();
                }
                

                //Open Connection to desired database
                string dataConnection = "Data Source=localhost;Initial Catalog=INB201;Integrated Security=True";
                SqlConnection connect = new SqlConnection(dataConnection);
                connect.Open();

                SqlCommand dataCommand = new SqlCommand();
                dataCommand.Connection = connect;

                //Loop through the arraylist and use a query to enter each value into the database
                for (int i = 0; i < 10; i = i + 2)
                {
                    int x = i;
                    int y = x + 1;
                    dataCommand.CommandText = "INSERT INTO testtable(user_name, user_id)";
                    dataCommand.CommandText += "VALUES ('" + sepList[x] + "','" + sepList[y] + "')";

                    //Execute database query and close the datareader
                    SqlDataReader datareader = dataCommand.ExecuteReader();
                    datareader.Close();
                }
                connect.Close();

                message = "Your file was imported successfully.";
                
            }
            catch
            {

                message = "Your file was not imported successfully.";
            }
            return message;
        }

        public string exportGroupDetails(string groupName)
        {
            string message;
            try 
            {
                string connectionString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=INB201;Integrated Security=True";
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    try
                    {
                        connection.Open();
                    }
                    catch (System.Data.SqlClient.SqlException)
                    {
                        // handle
                        message = "Your file was not exported successfully";
                        return message;
                    }
                    //NEED TO CHANGE OVER TO USER TABLE
                    //
                    //
                    //
                    string selectCommandText = "SELECT [user_id],[user_name],[phone_number],[email]  FROM users";
                    using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, connection))
                    {
                        using (DataTable table = new DataTable("users"))
                        {
                            adapter.Fill(table);
                            StringBuilder tabDelimitedText = new StringBuilder();
                            tabDelimitedText.AppendLine(groupName);
                            tabDelimitedText.AppendLine("User Id\tUser Name\tPhone Number\tEmail\t");
                            foreach (DataRow row in table.Rows)
                            {
                                string value = string.Format("{0}\t{1}\t{2}\t{3}", row[0], row[1], row[2], row[3]);
                                tabDelimitedText.AppendLine(value);
                            }

                            //Write all data to specified file
                            File.WriteAllText(@"C:\Users\n7473583\Desktop\exportfile.txt", tabDelimitedText.ToString());

                            //Notify user of successful export
                            message = "Your file has been exported successfully";
                        }
                    }

                    //export_groupTableAdapter exportAdapter = new export_groupTableAdapter();
                    //Membership.select_studentsDataTable LogonDetails = exportAdapter.GetData();

                    /* 
            Exec Master.dbo.Sp_Configure 'Show Advanced Options', 1

            Reconfigure

            Exec Master.dbo.Sp_Configure 'XP_CmdShell', 1

            Reconfigure
                    */

                    connection.Close();

                }
            }
            catch 
            {
                message = "Your file was no imported successfully";
            }

            return message;
        }

        /// <summary>
        /// Checks if student number has a group
        /// </summary>
        /// <returns>true iff student is in group</returns>
        public bool isInGroup()
        {
            bool inGroup = false;
            string groupName;

            if (this.group == null || this.group == "")
            {
                groupName = getGroupName();
            }
            else
            {
                groupName = this.group;
            }

            if (groupName == null || groupName == "")
            {
                return inGroup;
            }
            else
            {
                inGroup = true;
            }

            return inGroup;

        }

        /// <summary>
        /// Get group name of student
        /// </summary>
        /// <returns>String containing group name of student</returns>
        public string getGroupName()
        {
            // test if group name is already stored in memory
            // runs if there isn't
            if (this.group != null || this.group != "")
            {
                try
                {
                    string groupName = "";
                    SqlConnection connect = new SqlConnection(this.dataConnection);
                    SqlCommand dataCommand = new SqlCommand();
                    dataCommand.Connection = connect;
                    connect.Open();

                    dataCommand.CommandText = "SELECT group_name FROM users WHERE user_id = \'" + this.studentid + "\'";

                    groupName = ((string)dataCommand.ExecuteScalar());
                    this.group = groupName;
                    connect.Close();
                }
                catch (Exception e)
                {
                    // label on page is set to exception text
                    return e.ToString();
                }
            }

            // global data member 'group' is returned
            return this.group;
        }

        /// <summary>
        /// Retrieves a datatable containing group details of current student
        /// </summary>
        /// <returns>DataTable object containing group details</returns>
        public DataTable groupDetails()
        {
            DataTable dataTable = new DataTable();
            SqlConnection connect = new SqlConnection(this.dataConnection);
            SqlCommand dataCommand = new SqlCommand();
            dataCommand.Connection = connect;
            connect.Open();

            string groupName;

            // Quick check that there isn't a group stored in object memory,
            // if there is no group name saved getGroup() is called
            if (this.group == null || this.group == "")
            {
                groupName = getGroupName();
            }
            else
            {
                groupName = this.group;
            }

            dataCommand.CommandText = "SELECT * FROM users WHERE group_name = '" + groupName + "'";

            // Uses SqlDataReader object to execute command above
            // the data retrieved gets stored in dataTable variable of type DataTable
            SqlDataReader dataReader = dataCommand.ExecuteReader();
            dataTable.Load(dataReader);

            dataReader.Close();
            connect.Close();

            return dataTable;
        }


    }
}